/***************************************************************
*  Copyright (c) 2007 by GroupMe! Team (www.groupme.net)
*  All rights reserved.
*
*  This file is part of the GroupMe! Project. Source code of 
*  this project is closed and redistribution of this code is
*  prohibited. 
*  
*  Contact: http://www.groupme.net
*
*  This copyright notice MUST APPEAR in all copies of the file!
***************************************************************/
package net.groupme.search.strategy;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import net.groupme.exceptions.InsertFailedException;
import net.groupme.model.Group;
import net.groupme.model.MODEL_CONSTANTS;
import net.groupme.model.ObjectFactory;
import net.groupme.model.Resource;
import net.groupme.model.Tag;
import net.groupme.model.User;
import net.groupme.model.dao.DATABASE_CONSTANTS;
import net.groupme.search.strategy.model.WeightedIdentifiableComparatorByWeight;
import net.groupme.search.strategy.model.WeightedResource;
import net.groupme.utils.sql.GetWeightedResource;

/**
 * created on 19.12.2007
 * 
 * This class is the standard implementation of the {@link UserSpecificSearchStrategy} and
 * operates on a MySQL backend.
 * 
 * @author Fabian Abel, <a href="mailto:abel@l3s.de">abel@l3s.de</a>
 * @author last edited by: $Author: fabian $
 * 
 * @version $Revision: 1.2 $ $Date: 2008-11-01 20:18:05 $
 */
public class UserSpecificSearchStrategyImpl implements UserSpecificSearchStrategy{

	/** Logger for this class and subclasses */
	protected final  Log logger = LogFactory.getLog(this.getClass());

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForOwnAndSubscribedGroups(net.groupme.model.User, net.groupme.model.Tag)
	 */
	public List<Group> searchForOwnAndSubscribedGroups(User user, Tag tag) {
		// TODO Auto-generated method stub
		return null;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForOwnAndSubscribedResources(net.groupme.model.User, net.groupme.model.Tag)
	 */
	public List<Resource> searchForOwnAndSubscribedResources(User user, Tag tag) {
		return searchForResources(tag, user, null);
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForOwnGroups(net.groupme.model.User, net.groupme.model.Tag)
	 */
	public List<Group> searchForOwnGroups(User user, Tag tag) {
		// TODO Auto-generated method stub
		return null;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForOwnResources(net.groupme.model.User, net.groupme.model.Tag)
	 */
	public List<Resource> searchForOwnResources(User user, Tag tag) {
		return searchForResources(tag, user, MODEL_CONSTANTS.ROLE_OWNER);
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForSubscribedGroups(net.groupme.model.User, net.groupme.model.Tag)
	 */
	public List<Group> searchForSubscribedGroups(User user, Tag tag) {
		// TODO Auto-generated method stub
		return null;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForSubscribedResources(net.groupme.model.User, net.groupme.model.Tag)
	 */
	public List<Resource> searchForSubscribedResources(User user, Tag tag) {
		return searchForResources(tag, user, MODEL_CONSTANTS.ROLE_SUBSCRIBER);
	}

	@SuppressWarnings("unchecked")
	protected List<Resource> searchForResources(Tag tag, User user, String role){
		String roleSql = "";
		if(MODEL_CONSTANTS.ROLE_OWNER.equals(role)){
			roleSql = " AND " + DATABASE_CONSTANTS.USER_GROUP_USERGROUP_ROLE + " = '" + MODEL_CONSTANTS.ROLE_OWNER + "' ";
		}else if(MODEL_CONSTANTS.ROLE_SUBSCRIBER.equals(role)){
			roleSql = " AND " + DATABASE_CONSTANTS.USER_GROUP_USERGROUP_ROLE + " = '" + MODEL_CONSTANTS.ROLE_SUBSCRIBER + "' ";
		}else if(MODEL_CONSTANTS.ROLE_MODERATOR.equals(role)){
			roleSql = " AND " + DATABASE_CONSTANTS.USER_GROUP_USERGROUP_ROLE + " = '" + MODEL_CONSTANTS.ROLE_MODERATOR + "' ";
		}
		//Number of Users who tagged Resource X directly with tag:
		String query = 
			"SELECT tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ", " +
					"r." + DATABASE_CONSTANTS.RESOURCE_NAME + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_DESCRIPTION + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_URL + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_TYPE + ", " +
					"count(" + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") " + 
				" FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " as tas, " + DATABASE_CONSTANTS.TABLE_RESOURCE + " as r " +
				" WHERE tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "' " +
						
						//only resources of the user's group (with given role)
						" AND (" + 
							//tagged resource within the user's group?
							DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in " +
							"(SELECT distinct " + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + " FROM " +
						DATABASE_CONSTANTS.TABLE_GROUPRESOURCE  + " WHERE " + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " in " + 
						" ( Select " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP + " WHERE " + 
						DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + user.getId() + "' " + roleSql + " )) " +
							//tagged group of the user?
							"OR " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in " +
							" ( Select " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP + " WHERE " + 
							DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + user.getId() + "' " + roleSql + " )) " +
						//no groups:
//						" AND " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in " +
//						"(SELECT " + DATABASE_CONSTANTS.RESOURCE_ID + " FROM " + DATABASE_CONSTANTS.TABLE_RESOURCE  + 
//						" WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE + " != '" +  MODEL_CONSTANTS.RESOURCE_TYPE_GROUP + "')"  + 
						
						" AND tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " = r." +  DATABASE_CONSTANTS.RESOURCE_ID +
				" group by tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + 
				" order by count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") DESC";
		GetWeightedResource scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		Iterator iter = scoringQuery.execute().iterator();
		List<WeightedResource> resources = null;
		if (iter.hasNext()){
			resources = (List<WeightedResource>) iter.next();
		}
		//add In-Memory-Tags:
		addTagsToInMemoryResources(resources);
		
		//resources contained in directly tagged groups of the user:
		query = 
			"SELECT " + "gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_NAME + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_DESCRIPTION + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_URL + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_TYPE + ", " +
					"count(gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") " + 
				" FROM " + DATABASE_CONSTANTS.TABLE_GROUPRESOURCE + " as gr, " +
							DATABASE_CONSTANTS.TABLE_RESOURCE + " as r " +
						
						//only user's groups: 
				" WHERE gr." + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " in " +
				" ( Select distinct " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP + " WHERE " + 
				DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = '" + user.getId() + "' " + roleSql + " ) AND" +
				
				" gr." + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " in " +
					" ( SELECT distinct " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + 
				" WHERE " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "' AND " +
						    DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in " +
							"(SELECT " + DATABASE_CONSTANTS.RESOURCE_ID + " FROM " + DATABASE_CONSTANTS.TABLE_RESOURCE  + 
								" WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE + " = '" +  MODEL_CONSTANTS.RESOURCE_TYPE_GROUP + "')" + 
								" group by " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") AND " +
						" gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + " = r." +  DATABASE_CONSTANTS.RESOURCE_ID +
				" group by gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + 
				" order by count(gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") DESC";

		scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		iter = scoringQuery.execute().iterator();
		List<WeightedResource> groupResources = null;
		if (iter.hasNext()){
			groupResources = (List<WeightedResource>) iter.next();
		}
		//add In-Memory-Tags:
		addTagsToInMemoryResources(groupResources);
		
		//compute scoring:
		if(resources != null){
			if(groupResources != null){
				for(WeightedResource res: groupResources){
					if(resources.contains(res)){
						resources.get(resources.indexOf(res)).weight +=  ((SEARCH_STRATEGY_CONSTANTS.GROUP_CONTEXT_WEIGHT / SEARCH_STRATEGY_CONSTANTS.DIRECT_WEIGHT) * res.weight);
					}else{
						res.weight = ((SEARCH_STRATEGY_CONSTANTS.GROUP_CONTEXT_WEIGHT / SEARCH_STRATEGY_CONSTANTS.DIRECT_WEIGHT) * res.weight);
						resources.add(res);
					}
				}
			}
			Collections.sort(resources, new WeightedIdentifiableComparatorByWeight());

//			put resources of WeightedResources into a list:
			List<Resource> resultList = new ArrayList<Resource>();
			for(int i=0; i<resources.size(); i++){
				resultList.add(resources.get(i).resource);
			}
			return resultList;
		}else{ //will never happen :)
			return new ArrayList<Resource>();
		}
	}
	
	private void addTagsToInMemoryResources(List<WeightedResource> resources){
		if(resources == null){
			return;
		}
		String query = "SELECT tas.resourceId, t.id, t.keyword, t.description  FROM userTagGroupResource as tas, Tag as t WHERE tas.tagId = t.id AND tas.resourceId in ( ";
		for(int i=0; i < resources.size(); i++){
			query += resources.get(i).resource.getId();
			if(i != (resources.size() - 1)){
				query += ", ";
			}
		}
		query += ") group by t.id order by tas.resourceId";
		
		Statement sqlStatement = null;
		ResultSet resourceIdAndTags = null;
		try {
			sqlStatement = ObjectFactory.ds.getConnection().createStatement();
			resourceIdAndTags = sqlStatement.executeQuery(query);
			int resPosition = -1;
			String lastResId = null;
			String actualResId = null;
			while(resourceIdAndTags.next()){
				actualResId = resourceIdAndTags.getString(1);
				if(actualResId != lastResId){//set Pointer (resPosition):
					resPosition = -1;
					for(int i=0; i < resources.size() && (resPosition == -1); i++){
						if(resources.get(i).resource.getId().equals(actualResId)){
							resPosition = i;
						}
					}
				}
				Tag tag = ObjectFactory.getInMemoryTag(resourceIdAndTags.getString(2), resourceIdAndTags.getString(3), resourceIdAndTags.getString(4));
				try {
					resources.get(resPosition).resource.addTag(null, tag);
				} catch (InsertFailedException e) {
					logger.error("Actually, we do not want to insert something in a DB. We just want to store the tag of the resource in-memory.");
				}
			}
		} catch (SQLException e) {
			logger.error("Unable to connect to DB while querying for Tag Cloud.", e);
		}
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForResourcesDirectlyTaggedByGivenUserWithGivenTag(net.groupme.model.User, net.groupme.model.Tag)
	 */
	@SuppressWarnings("unchecked")
	public List<Resource> searchForResourcesDirectlyTaggedByGivenUserWithGivenTag(
			User user, Tag tag) {
		String query = "SELECT r.id, r.name, r.description, r.url, r.type, count(*) " +
				" FROM userTagGroupResource as tas, Resource as r WHERE tas.userId = " + user.getId() + " " +
				" and tas.tagId = " + tag.getId() + " and tas.resourceId = r.id group by r.id order by count(*) desc";
		GetWeightedResource scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		Iterator iter = scoringQuery.execute().iterator();
		List<WeightedResource> resources = null;
		if (iter.hasNext()){
			resources = (List<WeightedResource>) iter.next();
		}
		//add In-Memory-Tags:
		addTagsToInMemoryResources(resources);
		
		List<Resource> resultList = new ArrayList<Resource>();
		for(int i=0; i<resources.size(); i++){
			resultList.add(resources.get(i).resource);
		}
		return resultList;
	}

	/* (non-Javadoc)
	 * @see net.groupme.search.strategy.UserSpecificSearchStrategy#searchForResourcesDirectlyTaggedOrContainedInRelatedGroupOfUser(net.groupme.model.User, net.groupme.model.Tag)
	 */
	public List<Resource> searchForResourcesDirectlyTaggedOrContainedInRelatedGroupOfUser(
			User user, Tag tag) {
		//Number of Users who tagged Resource X directly with tag:
		String query = 
			"SELECT tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ", " +
					"r." + DATABASE_CONSTANTS.RESOURCE_NAME + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_DESCRIPTION + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_URL + ", " +
 					"r." + DATABASE_CONSTANTS.RESOURCE_TYPE + ", " +
					"count(" + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") " + 
				" FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + " as tas, " + DATABASE_CONSTANTS.TABLE_RESOURCE + " as r " +
				" WHERE tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "'" +				 												
						" AND tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " = r." +  DATABASE_CONSTANTS.RESOURCE_ID +
				" group by tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + 
				" order by count(tas." + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") DESC";
		GetWeightedResource scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		Iterator iter = scoringQuery.execute().iterator();
		List<WeightedResource> resources = null;
		if (iter.hasNext()){
			resources = (List<WeightedResource>) iter.next();
		}
		//add In-Memory-Tags:
		addTagsToInMemoryResources(resources);
		
		//resources contained in directly tagged groups of the user:
		query = 
			"SELECT " + "gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_NAME + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_DESCRIPTION + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_URL + ", " +
			 			"r." + DATABASE_CONSTANTS.RESOURCE_TYPE + ", " +
					"count(gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") " + 
				" FROM " + DATABASE_CONSTANTS.TABLE_GROUPRESOURCE + " as gr, " +
							DATABASE_CONSTANTS.TABLE_RESOURCE + " as r " +
				" WHERE gr." + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " in (" +
					" SELECT " + DATABASE_CONSTANTS.USER_GROUP_GROUP_ID + " FROM " + DATABASE_CONSTANTS.TABLE_USERGROUP + " where "
					+ DATABASE_CONSTANTS.USER_GROUP_USER_ID + " = " + user.getId() + " AND " + DATABASE_CONSTANTS.USER_GROUP_USERGROUP_ROLE + " = '" + MODEL_CONSTANTS.ROLE_OWNER + "' ) " +
							" AND" +
				   " gr." + DATABASE_CONSTANTS.GROUPRESOURCE_GROUP_ID + " in " +
					" ( SELECT distinct " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " FROM " + DATABASE_CONSTANTS.TABLE_USERTAGGROUPRESOURCE + 
				" WHERE " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_TAGID + " = '" + tag.getId() + "' AND " +
						    DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + " in " +
							"(SELECT " + DATABASE_CONSTANTS.RESOURCE_ID + " FROM " + DATABASE_CONSTANTS.TABLE_RESOURCE  + 
								" WHERE " + DATABASE_CONSTANTS.RESOURCE_TYPE + " = '" +  MODEL_CONSTANTS.RESOURCE_TYPE_GROUP + "')" + 
								" group by " + DATABASE_CONSTANTS.USERTAGGROUPRESOURCE_RESOURCEID + ") AND " +
						" gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + " = r." +  DATABASE_CONSTANTS.RESOURCE_ID +
				" group by gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + 
				" order by count(gr." + DATABASE_CONSTANTS.GROUPRESOURCE_RESOURCE_ID + ") DESC";
		scoringQuery = new GetWeightedResource(ObjectFactory.ds, query);
		iter = scoringQuery.execute().iterator();
		List<WeightedResource> groupResources = null;
		if (iter.hasNext()){
			groupResources = (List<WeightedResource>) iter.next();
		}
		//add In-Memory-Tags:
		addTagsToInMemoryResources(groupResources);
		
		//compute scoring:
		if(resources != null){
			if(groupResources != null){
				for(WeightedResource res: groupResources){
					if(resources.contains(res)){
						resources.get(resources.indexOf(res)).weight +=  ((SEARCH_STRATEGY_CONSTANTS.GROUP_CONTEXT_WEIGHT / SEARCH_STRATEGY_CONSTANTS.DIRECT_WEIGHT) * res.weight);
					}else{
						res.weight = ((SEARCH_STRATEGY_CONSTANTS.GROUP_CONTEXT_WEIGHT / SEARCH_STRATEGY_CONSTANTS.DIRECT_WEIGHT) * res.weight);
						resources.add(res);
					}
				}
			}
			Collections.sort(resources, new WeightedIdentifiableComparatorByWeight());
			List<Resource> resultList = new ArrayList<Resource>();
			for(int i=0; i<resources.size(); i++){
				resultList.add(resources.get(i).resource);
			}
			return resultList;
		}else{ //will never happen :)
			return null;
		}
	}
}
